# 1. 员工表(yuangong)有 FIRST_NAME VARCHAR(20)，LAST_NAME VARCHAR(20)，AGE INT，SEX VARCHAR(1)，INCOME FLOAT字段
# CREATE TABLE yuangong(
#   FIRST_NAME VARCHAR(20), LAST_NAME VARCHAR(20), AGE INT,
#   SEX VARCHAR(1), INCOME FLOAT
# )
import pymysql
conn = pymysql.connect(
    host="127.0.0.1", port=3306, user="root", passwd="tedu.cn",
    db="tedu1", charset="utf8"
)
cur = conn.cursor()
# ===========================================================
# 2. 使用 PyMySQL 创建该表  将表放入 tedu1 数据库中
# create_yg = """CREATE TABLE yuangong(
#     FIRST_NAME VARCHAR(20), LAST_NAME VARCHAR(20), AGE INT,
#     SEX VARCHAR(1), INCOME FLOAT
# )"""
# cur.execute(create_yg)
# ===========================================================
# 3. 使用 PyMySQL 添加以下三条数据
#    'Mac', 'A', 20, 'M', 20000
#    'Tom', 'B', 20, 'F', 30000
#    'Bob', 'C', 20, 'M', 40000
# insert_yg = "INSERT INTO yuangong VALUES (%s, %s, %s, %s, %s)"
# cur.executemany(
#     insert_yg, [
#         ('Mac', 'A', 20, 'M', 20000),
#         ('Tom', 'B', 20, 'F', 30000),
#         ('Bob', 'C', 20, 'M', 40000)
#     ]
# )
# ===========================================================
# 4. 使用 PyMySQL 查询所有用户信息，并打印结果
select_yg = "SELECT * FROM yuangong"
cur.execute(select_yg)
result = cur.fetchall()
print(result)
# ===========================================================
# 5. 将 FIRST_NAME  为 Mac 的用户工资(INCOME)改成 10000
# update_yg = "UPDATE yuangong SET INCOME = 10000 WHERE FIRST_NAME='Mac'"
# cur.execute(update_yg)
# 6. 删除 FIRST_NAME  为 Tom 的用户信息
# delete_yg = "DELETE FROM yuangong WHERE FIRST_NAME='Tom'"
# cur.execute(delete_yg)

conn.commit()
cur.close()
conn.close()